package com.mmy.dao.hotel;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.mmy.domain.hotel.VHotelBase;

public interface VHotelBaseRepository extends JpaRepository<VHotelBase, Integer> {
	/* 按搜索条件查询 */

	@Query(value = "select *  from vHotelBase where ( cHotelName like %:cHotelName%) and (:iZoneID = \"\" or iZoneID = :iZoneID)  and (cRate like %:cRate%)  and (:iType = \"\" or iType = :iType)", nativeQuery = true)
	List<VHotelBase> findBySearch(@Param("cHotelName") String strHotelName, @Param("iZoneID") String strZoneID,
			@Param("cRate") String strRate, @Param("iType") String strType);

	/**
	 * 函数功能说明： 进入后台酒店信息页面查询酒店详情信息
	 *
	 * @return 返回值说明 jsp页面
	 * @throws 异常处理说明
	 * @author 创建人 hanxl
	 * @date 创建日期 2020.12.24
	 */
	@Query(value = "select *  from vHotelBase where iHotelID=?1", nativeQuery = true)
	VHotelBase findByHotelID(int iHotelID);

	/* 前台默认通过区域查询 */
	@Query(value = "select *  from vHotelBase where left(iZoneID,2)=left(?1,2) ", nativeQuery = true)
	List<VHotelBase> findByZoneID(int zoneID);

	@Query(value = "select *  from vHotelBase where (cHotelName like %:cHotelName%) and   left(iZoneID,2)=left(:iZoneID,2)  and (:iType = \"\" or iType = :iType)", nativeQuery = true)
	List<VHotelBase> findBySearchHotel(@Param("cHotelName") String strHotelName, @Param("iZoneID") String strZoneID,
			@Param("iType") String strType);

	/**
	 * 前台景区电子票菜单显示景区信息
	 * 
	 * @return 返回值说明
	 * @author 创建人hanxl
	 * @param iPageSize
	 * @param iTempPageNo
	 * @date 创建日期 2021.03.02
	 */
	@Query(value = "(select * from vHotelBase where iHotelID in(:lstHotelID) and cHotelName like %:cHotelName%  and left(iZoneID,4)=left(:iZoneID,4) ) "
			+ "union (select * from vHotelBase where iHotelID in(:lstHotelID) and cHotelName like %:cHotelName%  ) "
			+ "union (select * from vHotelBase where iHotelID in(:lstHotelID) and left(iZoneID,4)=left(:iZoneID,4) ) "
			+ "union (select * from vHotelBase where iHotelID in(:lstHotelID)) limit :iTempPageNo,:iPageSize", nativeQuery = true)
	List<VHotelBase> findBySearch(@Param("lstHotelID") List<Integer> lstHotelID, @Param("iZoneID") int iZoneID,
			@Param("cHotelName") String strHotelName, @Param("iTempPageNo") int iTempPageNo,
			@Param("iPageSize") int iPageSize);

	/**
	 * 前台景区电子票菜单显示景区信息 分页
	 * 
	 * @return 返回值说明
	 * @author 创建人hanxl
	 * @param iPageSize
	 * @param iTempPageNo
	 * @date 创建日期 2020.12.24
	 */
	@Query(value = "select count(*)  from vHotelBase where iHotelID in(:lstHotelID)", nativeQuery = true)
	int countSearch(List<Integer> lstHotelID);

	/**
	 * 函数功能说明：前台首页景区模块显示 前8条
	 *
	 * @param 参数说明
	 * @return 返回值说明 jsp页面
	 * @throws 异常处理说明
	 * @author 创建人hanxl
	 * @date 创建日期2021.02.23
	 */
	@Query(value = "select * from vHotelBase where iHotelID in(:lstHotelID) and left(iZoneID,2)=left(:iZoneID,2) order by dCreateDate desc limit 8;", nativeQuery = true)
	List<VHotelBase> findHotel(List<Integer> lstHotelID, int iZoneID);

}
